const db = require('../config/database');
const { StatusCodes } = require('http-status-codes');
const AppError = require('../utils/appError');

class Employee {
  static async create({ name, phone, position, store_id, hire_date, status = 'active' }) {
    try {
      const [result] = await db.execute(
        'INSERT INTO employees (name, phone, position, store_id, hire_date, status, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW())',
        [name, phone, position, store_id, hire_date, status]
      );
      return this.findById(result.insertId);
    } catch (err) {
      throw new AppError('Failed to create employee', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async findById(id) {
    try {
      const [rows] = await db.execute(
        `SELECT e.*, s.name as store_name 
         FROM employees e 
         LEFT JOIN stores s ON e.store_id = s.id 
         WHERE e.id = ?`,
        [id]
      );
      if (rows.length === 0) {
        throw new AppError('Employee not found', StatusCodes.NOT_FOUND);
      }
      return rows[0];
    } catch (err) {
      if (err instanceof AppError) throw err;
      throw new AppError('Failed to find employee', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async findAll() {
    try {
      const [rows] = await db.execute(
        `SELECT e.*, s.name as store_name 
         FROM employees e 
         LEFT JOIN stores s ON e.store_id = s.id`
      );
      return rows;
    } catch (err) {
      throw new AppError('Failed to fetch employees', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async update(id, { name, phone, position, store_id, hire_date, status }) {
    try {
      await db.execute(
        'UPDATE employees SET name = ?, phone = ?, position = ?, store_id = ?, hire_date = ?, status = ?, updated_at = NOW() WHERE id = ?',
        [name, phone, position, store_id, hire_date, status, id]
      );
      return this.findById(id);
    } catch (err) {
      throw new AppError('Failed to update employee', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async delete(id) {
    try {
      await db.execute('DELETE FROM employees WHERE id = ?', [id]);
      return true;
    } catch (err) {
      throw new AppError('Failed to delete employee', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async findByStatus(status) {
    try {
      const [rows] = await db.execute(
        `SELECT e.*, s.name as store_name 
         FROM employees e 
         LEFT JOIN stores s ON e.store_id = s.id 
         WHERE e.status = ?`,
        [status]
      );
      return rows;
    } catch (err) {
      throw new AppError('Failed to fetch employees by status', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async findByPhone(phone) {
    try {
      const [rows] = await db.execute(
        `SELECT e.*, s.name as store_name 
         FROM employees e 
         LEFT JOIN stores s ON e.store_id = s.id 
         WHERE e.phone = ?`,
        [phone]
      );
      return rows[0];
    } catch (err) {
      throw new AppError('Failed to find employee by phone', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async findByStore(store_id) {
    try {
      const [rows] = await db.execute(
        `SELECT e.*, s.name as store_name 
         FROM employees e 
         LEFT JOIN stores s ON e.store_id = s.id 
         WHERE e.store_id = ?`,
        [store_id]
      );
      return rows;
    } catch (err) {
      throw new AppError('Failed to fetch employees by store', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async findByStatusAndStore(status, store_id) {
    try {
      const [rows] = await db.execute(
        `SELECT e.*, s.name as store_name 
         FROM employees e 
         LEFT JOIN stores s ON e.store_id = s.id 
         WHERE e.status = ? AND e.store_id = ?`,
        [status, store_id]
      );
      return rows;
    } catch (err) {
      throw new AppError('Failed to fetch employees by status and store', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }

  static async updateStatus(id, status) {
    try {
      await db.execute(
        'UPDATE employees SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, id]
      );
      return this.findById(id);
    } catch (err) {
      throw new AppError('Failed to update employee status', StatusCodes.INTERNAL_SERVER_ERROR);
    }
  }
}

module.exports = Employee;